package org.NooLab.docserver.storage;

import java.io.File;
import java.io.FileNotFoundException;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;

import org.h2.jdbc.JdbcSQLException;
import org.h2.server.Service;
import org.h2.tools.Server;
import com.iciql.Db;

 
import org.NooLab.utilities.files.DFutils;
import org.NooLab.utilities.logging.PrintLog;
import org.NooLab.utilities.net.GUID;
import org.NooLab.utilities.resources.ResourceLoader;
import org.NooLab.utilities.strings.ArrUtilities;



import org.NooLab.docobserver.structures.DocumentObserverIntf;
import org.NooLab.docserver.properties.DocoControlProperties;
import org.NooLab.docserver.properties.DocumentObserverProperties;
import org.NooLab.docserver.util.XmlStringHandling;


import org.NooLab.itexx.storage.ConnectorClientIntf;
import org.NooLab.itexx.storage.DatabaseMgmt;
import org.NooLab.itexx.storage.DbConnector;
import org.NooLab.itexx.storage.DbFieldRequest;
import org.NooLab.itexx.storage.DbFieldRequests;
import org.NooLab.itexx.storage.DbLogin;

import org.NooLab.itexx.storage.docserver.storage.DataBaseCreateCommand;
import org.NooLab.itexx.storage.docserver.storage.DataBaseHandler;
import org.NooLab.itexx.storage.docserver.storage.DataBaseHandlerIntf;
import org.NooLab.itexx.storage.docserver.storage.DocoDataBaseIntf;
import org.NooLab.itexx.storage.docserver.storage.MetaData;
import org.NooLab.itexx.storage.docserver.storage.ObservedDocuments;
import org.NooLab.itexx.storage.docserver.storage.db.iciql.Documents;
import org.NooLab.itexx.storage.docserver.storage.db.iciql.Folders;
import org.NooLab.itexx.storage.docserver.storage.db.iciql.Orphanes;




/**
 * 
 * 
 *
 */
public class DocoDataBase implements DocoDataBaseIntf,ConnectorClientIntf{

	String cfgResourceJarPath = "org/NooLab/docserver/resources/sql/" ; // trailing / needed !!
	
	DocumentObserverProperties  docoProperties ;
	private boolean dbFileExists;
	private boolean isOpen;
	boolean dbRecreated = false;
	// SQLstatements sqls ;
	
	
	String databaseName = "docoserv";
	String configFile = "docoserv.h2.db-config.xml";
	String storageDir = "" ; 
	int _DB_TARGET_LOCATING=0;
	
	String databaseUrl="";

	String accessMode = "tcp"; // "http" = external server via 8082, "file" 
	Server server;
	Connection connection;
	Db iciDb;
	DatabaseMetaData jdbMetaData ;
	String dbCatalog ;
	MetaData metaData ; 
	
	DataBaseHandlerIntf dbHandler ;
	private DbConnector dbConnector;
	
	
	String user = "sa";
	String password = "sa" ;
	
	String databaseFile="";
	
	DFutils fileutil = new DFutils();
	PrintLog out = new PrintLog(2,false);

	// ========================================================================
	public DocoDataBase( DocumentObserverProperties props ){
		docoProperties = props ;
		// sqls = new SQLstatements(props); 
		
		dbConnector = new DbConnector((ConnectorClientIntf)this);
		
		getStorageDir();
		out.setPrefix("[Doco]");
	}

	public DocoDataBase(){
		dbConnector = new DbConnector((ConnectorClientIntf)this);
		out.setPrefix("[Doco-2]");
	}

	// ========================================================================
	


	public boolean dbFileExists(){
		return dbFileExists;
	}
	
	public boolean isOpen(){
		return isOpen;
	}
	
	
	private boolean removeLock(){
		boolean rB=false;
		
		try{
		

			if (connection==null){
				ArrayList<String> locks = DFutils.listOfFiles("lock",".db", storageDir);
				for (int i=0;i<locks.size();i++){
					String fname = locks.get(i);
					if (DFutils.fileExists( fname)){
						fileutil.deleteFile( fname) ;
					}
				}
			}
			
		}catch(Exception e){
			e.printStackTrace();
		}
		
		return rB;
	}

	public boolean prepareDatabase() throws Exception {
		boolean rB=false;
		
		
		try {
			// remove any lock 
			if (connection==null){
				removeLock();
			}else{
				return true;
			}
			
			int servermodeFlag=0;
			if (accessMode.contentEquals("tcp")){
				// if in server mode
				createServer( databaseName,storageDir) ;
				servermodeFlag=1;
			}else{
				if (accessMode.contentEquals("http")){
					servermodeFlag=1;
				}
			}
			//  
			
			
			
			String h2Dir = DatabaseMgmt.setH2BaseDir(storageDir, _DB_TARGET_LOCATING) ;
			h2Dir = DFutils.createPath(h2Dir, "storage/") ;
			databaseFile = connect( databaseName, h2Dir, servermodeFlag ) ;
			
			
			
			rB = databaseFile.length()>0;
			
			if (rB){
				
				dbHandler = new DataBaseHandler( this ) ;
				
				if ((checkStructure(connection)==false) || (dbRecreated)){
					createDatabaseStructure() ;
				}else{
					metaData = dbHandler.getMetadata();
					metaData.retrieveMetaData();
					
					String str = ArrUtilities.arr2Text( metaData.getTableNames(1),";");

					out.printErr(2, "Tables : "+str) ;
				}
				
			}
			
		} catch (FileNotFoundException e) {
			rB=false;
			e.printStackTrace();
		}
		 
		return rB;
	}

	//             e.g. resourceName = "create-db-sql-xml" ;
	private String getConfigResource( String resourceName ) throws Exception{
		
		String resourcePath = cfgResourceJarPath + resourceName;
		
		String xmlstr = "" ;
		boolean rB;
		
		ResourceLoader rsrcLoader = new ResourceLoader();   
		rB = rsrcLoader.loadTextResource( this.getClass(), resourcePath  ) ;
		if (rB){  
			xmlstr = rsrcLoader.getTextResource();
			
		}else{
			throw(new Exception("unable to load resources (create-db-sql-xml)")) ;
		}
		
		return xmlstr ;
	}

	
	private void createDatabaseStructure() throws Exception {
		// loading the  "create-db-sql-xml"  
		// from package "org.NooLab.docserver.resources.sql"
		
		String xmlstr = "" ;
		
		xmlstr = getConfigResource("create-db-sql-xml");
		
		connect( databaseName,storageDir) ;
		
		// get the create statements
		ArrayList<DataBaseCreateCommand> ccs = dbHandler.getCreateFromResource( xmlstr ); 
		
		// executing the statements
		dbHandler.createDataTablesByResource(ccs);
		
		create_iTexxUsers(connection);
		
	}

	
	
	
	public void create_iTexxUsers(Connection c) {
		
		Statement statement;
		String sql;
		String texxUser = "DROP USER IF EXISTS TEXX; " +
		                  "CREATE USER TEXX PASSWORD 'texx' ADMIN;";
		String texxRights = "GRANT SELECT,INSERT,UPDATE ON documents,folders,orphanes TO PUBLIC;";
						// SELECT,INSERT,UPDATE | ALL
		// "GRANT READONLY TO PUBLIC;"+
		
		try {
		
			statement = c.createStatement();
			statement.execute( texxUser );  
			c.commit() ;
			
			statement = c.createStatement();
			// statement.execute( texxRights );  

			//connection.commit() ;
			// SELECT * FROM INFORMATION_SCHEMA.RIGHTS
			// GRANTEE  	GRANTEETYPE  	GRANTEDROLE  	RIGHTS  	TABLE_SCHEMA  	TABLE_NAME  	ID  
			sql = "SELECT ID FROM INFORMATION_SCHEMA.USERS WHERE NAME ='TEXX';" ;
			
			
			statement.close();
		
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	private boolean checkStructure(Connection c) {
		
		ArrayList<String> tnames = new ArrayList<String>();
		String xmlstr;
		boolean rB=false;
		int expectedTableCount = 9999 ;
		
		Vector<Object> nodeObjs = null;
		XmlStringHandling xMsg = new XmlStringHandling();
		// read definition file 
		
		try {
			if (c!=null){
				 // open(c) ;
			}else {
				open();
			}
		
			jdbMetaData = c.getMetaData() ;
			if (metaData==null){
				dbHandler.updateMetaData(c);
				metaData = dbHandler.getMetadata() ; 
			}
			tnames = metaData.getTableNames(1) ;
			
			xmlstr = getConfigResource("create-db-sql-xml");
			xMsg.setContentRoot("documentobserver") ;
			
			nodeObjs = xMsg.getNodeList(xmlstr, "/documentobserver", "table");
			
			if (nodeObjs!=null){
				expectedTableCount = nodeObjs.size() ;
			}
				
			if (tnames.size() < expectedTableCount){
				// close();
				return rB;
			}
			
			for (int i=0;i<nodeObjs.size();i++){
				String tname = xMsg.getNodeInfo(nodeObjs.get(i), "/table", "name") ;
					// ((Node)nodeObjs).
				int p = tnames.indexOf(tname.toUpperCase()) ;
				if (p<0){
					rB=false;
					return rB;
				}
			}
			
			rB=true;
			
		} catch (Exception e) {
			
			e.printStackTrace();
		}
		
		
		return rB;
	}


	
	public void close() {
		
		try{

			jdbMetaData = null;
			dbCatalog = "";
			disconnect();
			if (server != null){
				server.stop() ;
			}
		
		}catch(Exception e){
			
		}
		
	}

	protected void getJdbMeta(Connection c) throws SQLException{
		jdbMetaData = c.getMetaData();
		dbCatalog = c.getCatalog() ;
		
	}

	public void setDbMetaData(DatabaseMetaData dbMetaData) {
		jdbMetaData = dbMetaData;
	}

	public void updateInfraStructure(Connection c ) throws Exception{

		getJdbMeta(c);
		
		dbHandler.setiDb( iciDb ) ;

		// creates a new instance for new MetaData and retrieves the latest info again
		dbHandler.updateMetaData(c) ; 
	
	}
	
	
	public void open(Connection c) throws Exception{
		
		iciDb = Db.open(c);
		updateInfraStructure(c);
	}

	public void open() throws Exception{
		
		iciDb = Db.open(databaseUrl, user, password);
		connection = iciDb.getConnection();

		updateInfraStructure(connection);
	}

	  	
	public void startServer() {
		
		removeLock();
		try {
			prepareDatabase();
		} catch (Exception e) {
			
			e.printStackTrace();
		}
		
	}

	/*
SELECT * FROM INFORMATION_SCHEMA.ROLES ;
SELECT * FROM INFORMATION_SCHEMA.RIGHTS ;
SELECT * FROM INFORMATION_SCHEMA.USERS ;
SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES ;
	 */
	public Connection getWireLessDatabaseServer(String user, String password){
		
		String url;
		String h2Dir ,storDir;
		
		Connection c = null;
		
		try {
			// "jdbc:h2:"+
			// System.setProperty("h2.baseDir", storageDir);

			
			h2Dir = DatabaseMgmt.setH2BaseDir( storageDir, _DB_TARGET_LOCATING); //DatabaseMgmt._BASEDIR_QUERY_STOR);
			// String storDir = storageDir;
			storDir = DFutils.createPath( h2Dir, "storage/") ;
			
			if (storDir.endsWith("/")){
				storDir = storDir.substring(0,storDir.length()-1); 
			}
			url = 	"jdbc:h2:tcp://localhost/"+(storDir)+"/"+databaseName ;
			
			DbLogin login = new DbLogin(user,password) ; 
			c = dbConnector.getConnection(url, login);
			connection = c;
			// Class h2Driver = Class.forName("org.h2.Driver");
			// c = DriverManager.getConnection( url, user, password);
			// connection = c;
			if (c!=null){
				databaseUrl = url ;
			}
			dbHandler = new DataBaseHandler( this ) ;
			
		}catch(Exception e){
			e.printStackTrace() ;
		}
		return c;
	}
	
	public String createServer(String dbname, String filepath){
		

		// 
		// start the TCP Server
		String url;
		
		try {
			/* this is indeed global, across ALL applications, 
			   so we have to use a global dir inside iTexx
			   
			   storage is on the same level as the main applications sub dir
			   iTexx/storage
			*/ 
			
			String h2Dir = DatabaseMgmt.setH2BaseDir( storageDir, _DB_TARGET_LOCATING);// DatabaseMgmt._BASEDIR_QUERY_STOR);

			// String storDir = storageDir;
			String storDir = DFutils.createPath( h2Dir, "storage/") ;
			
			
			
			if (storDir.endsWith("/")){
				storDir = storDir.substring(0,storDir.length()-1); 
			}

			
			
			url = 	storDir +"/"+dbname+".h2.db" ; // +";AUTO_SERVER=TRUE" ;
			url = url.replace("//", "/") ;

			if (DFutils.fileExists(url)==false){
				dbRecreated = true;
				connect(dbname, filepath);
				connection.close();
				connection = null ;
			}
			String[] args = new String[]{"-tcpAllowOthers", "-tcpPort","8051"} ;
			server = Server.createTcpServer( args ); // storageDir, as first ?

			try{
				
				server.start() ;
				int port = server.getPort();
				Service srvc = server.getService();
				boolean allowOthers = srvc.getAllowOthers() ;
				
				if (server.isRunning(true)){
					out.print(2, "H2 server for database <"+dbname+"> is running on port "+port) ;
				}
				
			}catch(Exception e){
				
			}

			 
			
		} catch (Exception e) {
			
			e.printStackTrace();
		}
		//createTcpServer(args).start();

		// stop the TCP Server
		// server.stop();


		return "";
	}

	
	public String connect( String dbname, String filepath) throws FileNotFoundException{
		return connect( dbname, filepath, 0);
	}
	public String connect( String dbname, String filepath, int serverMode) throws FileNotFoundException{
		
		String dbfile ="";
		int err=0;
		
	/* parameters 
        ";MODE=MYSQL"
		";FILE_LOCK=FS" +
		";PAGE_SIZE=1024" +
		";CACHE_SIZE=8192";
	    DB_CLOSE_DELAY=10
     */
		
		
		try{
 			
			if ((connection!=null) && (connection.isClosed()==false)){
				dbfile = DFutils.createPath(filepath,dbname+".h2.db") ;
				if (DFutils.fileExists(dbfile)){
					return dbfile ;
				}
			}
			
			// "jdbc:h2:tcp://localhost/~/docoserv"
			// :nio
			
			String h2Dir = DatabaseMgmt.setH2BaseDir( storageDir, _DB_TARGET_LOCATING);// DatabaseMgmt._BASEDIR_QUERY_STOR);
			
			String _db_dir = DFutils.createPath( h2Dir, "storage/") ;
			// String _db_dir = storageDir;
			
			
			if (_db_dir.endsWith("/")){
				_db_dir = _db_dir.substring(0,_db_dir.length()-1);
			}

			// done in DatabaseMgmt.... System.setProperty("h2.baseDir", _db_dir);
			
			
			String url = 	"jdbc:h2:"+_db_dir+"/"+dbname; // +";AUTO_SERVER=TRUE" ;
			url = url.replace("//", "/") ;

			if (serverMode>=1){
				url = 	"jdbc:h2:tcp://localhost/"+_db_dir+"/"+dbname;
			}
			
			Class h2Driver = Class.forName("org.h2.Driver");
	        
			connection = DriverManager.getConnection( url, user, password);

			dbfile = DFutils.createPath(filepath,dbname+".h2.db") ;
			
			File fil = new File(dbfile);
			if (fil.exists()){
				if (connection.isClosed()==false){
					databaseUrl = url;
				}
				
			}
			
			 
			// CALL DATABASE_PATH(); 
			 
			
			if (fil.exists()==false){
				throw new FileNotFoundException("\nDatabase file not found (err="+err+")\n"+
											    "expected directory : " + filepath+"\n"+
											    "expected db file   : " + dbname+".h2.db\n");
			}

			out.print(1,"database has been started ...");
			out.print(2,"...its connection url is : "+ databaseUrl) ;
			
		}catch(JdbcSQLException jx){
			System.out.println("Connecting to database <"+dbname+"> failed \n"+jx.getMessage() );
			String lockfile = filepath+"/"+dbname+".lock.db";
			File fil = new File(lockfile);
			fil.deleteOnExit();
			fil.delete();
			
			dbfile = "";
			
		}catch( SQLException sx){
			System.err.println ("Cannot connect to database server");
			dbfile = "";
			sx.printStackTrace();
			
		}catch( ClassNotFoundException e){
			dbfile = "";
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
		
		 
		return dbfile;
	}


	public void checkForDbAvailability() {
		
		try {
			
			if (connection.isClosed()){
				connect( databaseName, storageDir) ;
			}

			if (iciDb==null){
				open( connection );
			}
			
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}

	public void disconnect(){
		
		try{
			
			if (connection != null){
				
				if (connection.isClosed()==false){
					connection.commit();
					
					out.delay(100);
					
					connection.close(); 
				}
				
				out.delay(100);
				if (connection.isClosed()){
					connection = null;
				}
			}
			
		}catch( SQLException sx){
			sx.printStackTrace();
		}finally {
	         
	    }
		
	}

	public Connection getConnection(){
		
		return connection;
	}
	
	
	public String getStorageDir( ){
		
		try{

			String systemroot = docoProperties.getSystemRootDir();   // [path]/iTexx  
			//if (docoProperties.getApplicationContext()
			//    .contentEquals( DocumentObserverProperties._APP_CONTEXT_ITEXX)){
			
			String appcontext = docoProperties.getApplicationContext() ; 
			
			if (appcontext.contains(DocumentObserverProperties._APP_CONTEXT_ITEXX)){ 
				// itexx (as service within iTexx) or "standalone" (via applet)
				String storageroot = systemroot;
				
				if (storageroot.contains("/DocumentServer")){
					storageroot = DFutils.getParentDir(storageroot, 2);
				}
				
				storageDir = DFutils.createPath(storageroot, "storage/") ;
				_DB_TARGET_LOCATING = 0;
			}else{
				// the storage is subject of the project
				String prj = docoProperties.getPersistenceSettings().getProjectName() ;
				storageDir = DFutils.createPath(systemroot, prj+"/") ;
				storageDir = DFutils.createPath(storageDir, "storage/") ;
				
				_DB_TARGET_LOCATING = 0; // DatabaseMgmt._BASEDIR_QUERY_PROJECT;
			}
			
			

		}catch(Exception e){
			
		}
		return storageDir ; 
	}
	
	public void setStorageFolder(String dir) {
		
		storageDir = dir;
	}

	private String getDBhomefile( String dbname){
		
		String filename="",filepath, user_home;
			
		getStorageDir();
		filepath = DFutils.createPath(storageDir, dbname);
		
		return filepath;
	}

	public DocumentObserverProperties getDocoProperties() {
		return docoProperties;
	}

	public String getDatabaseName() {
		return databaseName;
	}

	public String getDatabaseUrl() {
		return databaseUrl;
	}

	public Db getIciDb() {
		return iciDb;
	}

	public DatabaseMetaData getDbMetaData() {
		return jdbMetaData;
	}

	public String getDbCatalog() {
		return dbCatalog;
	}

	public DataBaseHandlerIntf getDbHandler() {
		return dbHandler;
	}

	public String getDatabaseFile() {
		return databaseFile;
	}

	public PrintLog getOut() {
		return out;
	}

	

	/**
	 * 
	 * called from DocumentObserver : prepareObservations()
	 * 
	 * @param filename
	 * @param folderid
	 * @param accvalues
	 * @return
	 */
	public long insertUpdateDocuments(String filename, long folderid, DbFieldRequests accvalues ) {
		// 
		
		long docid = -7, size=0;
		DbFieldRequests doc_accvalues=null ;
		List<Documents> sDoc = null ;
		
		if (folderid<0){
			return -3;
		}
		
		try {
			
			doc_accvalues = new DbFieldRequests( getIciDb() );
			
		} catch (Exception e) {
			e.printStackTrace();
		}

		size = (Long)accvalues.getfieldRequestValue("size") ;
		// get changedate of folder
		
		docid  = getDocumentKey("documents", filename, folderid, doc_accvalues ) ;
		
		if (docid<0) {
			// insert 
			
			filename = DFutils.getName(filename);
			docid = insertDocument("documents", filename, size, folderid, DocumentObserverIntf._STATUS_FILE_REGISTERED ) ;
			
		}else{
			// update the changedate of the doc
			Long changedate = (Long)accvalues.getfieldRequestValue("changedate") ;
			Documents d = new Documents();
			try{
				sDoc = iciDb.from(d).where(d.docid).is(docid).select();
				if ((sDoc!=null) && (sDoc.size()>0)){
					// check whether size is >0 date != null
					
					
					if ((sDoc.get(0).changedate==null) && (changedate!=null)){
						doc_accvalues.clear();
						doc_accvalues.put("changedate", changedate);
						doc_accvalues.put("size", size);
						updateDocument("documents", docid, doc_accvalues ) ;	
					}
					if (sDoc.get(0).size <=1) {
						// this update only if status<=1
						doc_accvalues.clear();
						doc_accvalues.put("size", size);
						updateDocument("documents", docid, doc_accvalues ) ;
					}
					
				} // known ?
			}catch(Exception e){
				
			}
		}
		
		
		return docid ;
	}
	
	

	public long getDocumentKey( String tablename, final String _itemValue, final long _folderid, DbFieldRequests accvalues) {
		long dbKey = -1L;
		List<Documents> rDocSet = null ;
		 
		if (tablename.toLowerCase().contentEquals("documents")){
			final Documents d = new Documents(); 
	
			// 
			
			
			try{
				
				// registered = iciDb.from(d).where(d.name).like(_itemValue).and(d.folderid).is(_folderid).select();
				
				rDocSet = iciDb.from(d).where(d.name).is(_itemValue).and(d.folderid).is(_folderid).select();
				/*
			    registered = iciDb.from(d).orderBy(d.folderid)
			                                     .select( (new Documents(){{ d.folderid = _folderid ;
			      			                			                     d.name     = _itemValue; }}) );
				*/
			
			
				if ((rDocSet != null) && (rDocSet.size() > 0)) {
					dbKey = rDocSet.get(0).docid;
				}
	
			}catch(Exception e){
				// exception if no result
				e.printStackTrace();
			}
	
		}
	
		    
		
		return dbKey;
	}

	/*
	 * SELECT COUNT(*) FROM table_name
	 * 
	 * db.from(p).count(*)
	
	 * 
	 */
	

	private boolean updateDocument(String tablename, long docid, Long changedate) {

		boolean rB=false;
	
		try{
	
			if (tablename.toLowerCase().contentEquals("documents")){
				Documents d = new Documents();
				
				if (changedate!=null){
					iciDb.from(d).set(d.changedate).to(changedate)
		        		     	 .where(d.docid).is(docid).update();
				}

			}
			
		}catch(Exception e){
			e.printStackTrace();
		}
		
		return rB;
	}
	
	public boolean updateDocumentMulti(String tablename, final long _docid, DbFieldRequests accvalues) {
		boolean rB=false;
		
		Object obj;
		DbFieldRequest rcv ;
		String str;
		List<Documents> rset ;
		
		long size, fid, cdate;
		int status;
		
		try{
			Documents d = new Documents();

			status = (Integer)accvalues.getfieldRequestValue("status");
			size   = (Long)accvalues.getfieldRequestValue("size");
			fid    = (Long)accvalues.getfieldRequestValue("folderid");
			cdate  = (Long)accvalues.getfieldRequestValue("changedate");

			iciDb.from(d).set(d.size).to(size)
			             .set(d.changedate).to(cdate)
			             .set(d.folderid).to(fid)
			             .set(d.status).to(status)
			             .where(d.docid).is(_docid).update();
			
		}catch(Exception e){
			e.printStackTrace();
		}
		
		return rB;
	}

	public boolean updateDocumentRename( String tablename, final long _docid, 
										 String newFilename, 
										 int statusFolderChanged) {
		boolean rB=false;
		
		Object obj;
		DbFieldRequest rcv ;
		String str;
		List<Documents> rset ;
		
		long size, fid, cdate;
		int status;
		
		try{
			Documents d = new Documents();


			iciDb.from(d).set(d.name).to(newFilename)
			             .set(d.status).to(statusFolderChanged)
			             .set(d.changedate).to(System.currentTimeMillis())
			             .where(d.docid).is(_docid).update();
			
		}catch(Exception e){
			e.printStackTrace();
		}
		
		return rB;
	}

	
	public boolean updateDocument(String tablename, final long _docid, DbFieldRequests accvalues) {
		boolean rB=false;
		
		Object obj;
		DbFieldRequest rcv ;
		String str;
		List<Documents> rset ;
		
		try{
	
			if (tablename.toLowerCase().contentEquals("documents")){
				Documents d = new Documents();
				
				for (int i=0;i<accvalues.size();i++){
					rcv = accvalues.getItems().get(i) ;
					
					str = rcv.getFieldname();
					obj = rcv.getFieldValue() ;
					
					rset = iciDb.from(d).where(d.docid).is(_docid).select() ;
					if ((rset!=null) && (rset.size()>0)){
						
						if ((str.contentEquals("size")) && ((Long)obj>0)){
							if (obj!=null){
								iciDb.from(d).set(d.size).to((Long)obj)
				        		     	 	 .where(d.docid).is(_docid).update();
								rB=true;
							} // <> null ?
							
						}
						
						
						if ((str.contentEquals("changedate")) && ((Long)obj>0)){
							if (obj!=null){
									iciDb.from(d).set(d.changedate).to((Long)obj)
					        		     	 	 .where(d.docid).is(_docid).update();
									rB=true;
							} // <> null ?
						} // changedate ?

					} // any ?
				}// i->

			}
			
		}catch(Exception e){
			e.printStackTrace();
		}
		
		return rB;
	}
	
	public long insertDocument( String tablename, String nameValue, long size, 
								long hostFolderId , int statusflag) {
		boolean rB=false;
		long dbKey = -1;
	
		try{
			
			if (tablename.toLowerCase().contentEquals("documents")){
				Documents d = new Documents();
				
				d.guid   = GUID.randomvalue() ;
				d.name   = nameValue ;
				d.size   = size ;
				d.language = "" ;
				d.vanishtime = 0L;
				d.folderid = hostFolderId ;
				d.changedate = System.currentTimeMillis() ; // better file date....
				d.status = statusflag ;
				d.mime   = "";
				dbKey = iciDb.insertAndGetKey( d );
			}
			
		}catch(Exception e){
			e.printStackTrace();
			dbKey = -7;
		}
		
		return dbKey;
	}

	public long insertDocumentFull(String tablename, String filename, DbFieldRequests fieldRequests) {
		
		long dbKey = -1, size,statusflag, folderid;
		Documents d = new Documents();

		try{
			d.size = (long) ((Long) fieldRequests.getfieldRequestValue("size"));
			d.folderid = (long) ((Long) fieldRequests.getfieldRequestValue("folderid"));
			d.status   = (int) ((Integer) fieldRequests.getfieldRequestValue("status"));

			d.guid   = GUID.randomvalue() ;
			d.name   = filename ;
			d.changedate = System.currentTimeMillis() ; // better file date....

			dbKey = iciDb.insertAndGetKey(d);
	
		}catch(Exception e){
			e.printStackTrace();
			dbKey = -7;
		}
		
		return dbKey;

	}

	public long getItemKey( String tablename, String itemValue, DbFieldRequests accvalues) {
		long dbKey = -1L;
		
		if (tablename.toLowerCase().contentEquals("folders")){
			Folders f = new Folders(); 
			
			List<Folders> registered = iciDb.from(f).where(f.location).is(itemValue).select();
			
			if ((registered!=null) && (registered.size()>0)){
				
				dbKey = registered.get(0).id ;
			}
		}

		return dbKey;
	}


	public boolean checkForItem( String tablename, String itemValue, DbFieldRequests accvalues) {
		// 
		boolean rB=false;
		// dbHandler.iciqInsert( iciDb, );

		// Customer c = new Customer();
		// List<Customer> waCustomers = db.from(c). where(c.region).is("WA").select();
		if (tablename.toLowerCase().contentEquals("folders")){
			Folders f = new Folders(); // should be in metadata
			
			// TODO: we should find all classes that implement AliasIntf and list them in MetaData
			//       in order to avoid multiple instantiations (if we want to do so)
			MetaData metaData = dbHandler.getMetadata() ;
			
			List<Folders> registered = iciDb.from(f). where(f.location).is(itemValue).select();
			if (registered.size()>0){
				rB=true;
			}
			
			// iciDb.from(f).selectCount();
		}
		if (tablename.toLowerCase().contentEquals("documents")){
			
		}
		if (tablename.toLowerCase().contentEquals("orphanes")){
			
		}

		/*
		List<Product> restock =
		        db.from(p).
		        where(p.unitsInStock).
		        is(0).orderBy(p.productId).select();
		                
		for (Product product : restock) {
		        db.from(p).
		        set(p.unitsInStock).to(25).
		        where(p.productId).is(product.productId).update();
		}
		*/
		
		return rB;
	}

	/*
	 * SELECT COUNT(*) FROM table_name
	 * 
	 * db.from(p).count(*)

	 * 
	 */
	
	public boolean updateFolder(String tablename, String nameValue, DbFieldRequests accvalues ) throws Exception {
		boolean rB=false,paramOk;
		DbFieldRequest request;
		Exception et=null;
		
		if (iciDb==null){
			throw(new Exception("iciDb is null, no mediated database operation possible (updateFolder) ")) ;
		}
		if (tablename.toLowerCase().contentEquals("folders")){
			Folders f = new Folders();

			f.location = nameValue ;
			paramOk = true;

			try{
				// iciDb.update(f);
				
				String sql = iciDb.from(f).set(f.changedate).toParameter().where(f.location).is(nameValue).toSQL();
				
				Long value = ((Long) (accvalues.getfieldRequest(0).getFieldValue()));

				iciDb.executeUpdate(sql, value) ;
				
				/*
				for (int i=0;i<accvalues.size();i++){
					request = accvalues.getfieldRequest(i) ;
					if (paramOk){ 
					
						// paramOk = f.setValue( request ) ;
						Field dbField = f.getFieldByName( request.fieldName) ;
						iciDb.from(f).set( f.changedate
								).to( (Long) request.fieldValue ) 
	        	         	         .where(f.location).is(nameValue).update();

					}
				}

				 */
				
				rB=true;
			}catch(Exception e){
				// there is an exception if the update fails
				et = e;
			}
			if (et!=null){
				throw(et);
			}
		}
		
		return rB ;
	}

	
	public long insertFolder(String tablename, String nameValue, DbFieldRequests fieldrequest ) {
		boolean rB=false;
		long dbKey = -1;
		
		try{

			if (tablename.toLowerCase().contentEquals("folders")){
				Folders f = new Folders();

				f.guid = GUID.randomvalue() ;
				f.location = nameValue ;
				f.status = DocumentObserverIntf._STATUS_FOLDER_REGISTERED ;
				
				
				dbKey = iciDb.insertAndGetKey( f );
				
			}
			
		}catch(Exception e){
			e.printStackTrace();
			dbKey = -7;
		}
		
		return dbKey;
	}

	public void removeEntry(String tablename, long entryid) {
		
		if (tablename.toLowerCase().contentEquals("folders")){
			Folders f = new Folders();
			iciDb.from(f).where(f.id).is(entryid).delete();
		}
		if (tablename.toLowerCase().contentEquals("documents")){
			Documents d = new Documents();
			iciDb.from(d).where(d.docid).is(entryid).delete();
		}
		if (tablename.toLowerCase().contentEquals("orphanes")){
			// Orphanes o = new Orphanes();
			// iciDb.from(o).where(o.id).is(entryid).delete();

		}
		
	}
	
	public void removeDoubleFolderEntries( ) {
		
	}
	
	public void orphaneVanishedDocumentEntries(){
		
		String tablename = "documents" , filepath, dname, hostFolder;
		Documents d = new Documents();
		Folders f = new Folders();
		Orphanes orph = new Orphanes();
		
		List<Documents> docsInTable;
		long fid , docid;

		
		try{
			

			docsInTable = iciDb.from(d).select() ;
			
			for (int n=0;n<docsInTable.size();n++){

				dname = docsInTable.get(n).name ;
				docid = docsInTable.get(n).docid ;
				fid = docsInTable.get(n).folderid ;

				if (fid>0){

					hostFolder = "";
					try{
				
						hostFolder = iciDb.from(f).where(f.id).is(fid).select().get(0).location ;
						
						filepath = DFutils.createPath( hostFolder, dname);
						
						if (DFutils.fileExists(filepath)==false){
							
							iciDb.from(d).where(d.docid).is(docid).delete() ;
							
							try{
								orph.docid = docid;
								orph.folderid = fid;
								orph.filename = dname ;
								orph.status   = DocumentObserverIntf._STATUS_FILE_REGISTERED ;
								iciDb.insert(orph);
								
							}catch(Exception e){
							}
							
						}
						
					}catch(Exception e){
						e.printStackTrace();
					}
					
				}

			}
	
			
		}catch(Exception e){
			e.printStackTrace();
		}
				
	}
	
	
	public void removeDoubleDocumentsEntries( ) {
		String tablename = "documents" ;
		Documents d = new Documents();
		List<Documents> distinctPerFolder, docsPerFolder;
		List<Long> fids;
		List<String> distinctDocnames;
		
		try{
			// SELECT DISTINCT column_name(s) FROM table_name
			// statement with binding to the model class
			//distinctPerFolder = iciDb.executeQuery(Documents.class, "select distinct folderid from documents");
			// distinctPerFolder
			fids = iciDb.from(d).selectDistinct(d.folderid) ;
			
			for (int i=0;i<fids.size();i++){
				long fid = fids.get(i) ;
				
				distinctDocnames = iciDb.from(d).where(d.folderid).is(fid).selectDistinct(d.name) ;
				
				try{
					if (distinctDocnames.size()>1){
						// test all names for doubles
						for (int n=0;n<distinctDocnames.size();n++){

							String dname = distinctDocnames.get(n) ;  
							docsPerFolder = iciDb.from(d).where(d.folderid).is(fid).and(d.name).is(dname).select();
							
							if (docsPerFolder.size()>1){
								for (int r=1;r<docsPerFolder.size();r++){
									long did = docsPerFolder.get(r).docid ;
									iciDb.from(d).where(d.docid).is(did).delete() ;	
								}
							}

						}	
					} // >1 of the same name ?
					
				}catch(Exception e){
					
					e.printStackTrace();
				}
			}
			
		}catch(Exception e){
			e.printStackTrace();
		}
		
		
	}

	public void removeExcludedEntries(DocoControlProperties dcp) {
		
		// remove documents with excluded extensions, or of excluded type ...
		
		
		// remove folders (and their docs) if they are actually excluded
		
	}

	public void clearTable(String tablename) {
		// 
		
		Documents d = new Documents();
		
		if (iciDb!=null){
			iciDb.from(d).delete() ;
		}

	}

	/**
	 * 
	 * 
	 * @param maxCount
	 * @param mode  0=file with changed content, 1=moved file,  2=renamed file,  -1=deleted file
	 * @return
	 */
	public ObservedDocuments getDocumentsDue(int maxCount, int mode, DbFieldRequests accvalues) {
		
		ObservedDocuments docs = new ObservedDocuments ();  
		// ArrayList<String> docs = new ArrayList<String> ();
		
		String filename, filepath ; 
		
		Documents d = new Documents(), dr ;
		Folders   f = new Folders();
		
		List<Documents> docRecords = null;
		List<Folders> folderRecords= null;
		List<String> folderNames = null;

		
		try{
			if (connection.isClosed()==false){
				open(connection) ; // defines also iciDb...
			}else{
				open();
			}

			try{
				
				if (mode == 0){
					docRecords = iciDb.from(d).where(d.status).is( DocumentObserverIntf._STATUS_FILE_REGISTERED ) // 1
											     .or(d.status).is( DocumentObserverIntf._FILE_CHANGE_MOD ) // 9
											     .or(d.status).is( DocumentObserverIntf._FILE_CHANGE_NEW)  // 10
											     .select();
					/*
					String sql = iciDb.from(d).where(d.status).is( DocumentObserverIntf._STATUS_FILE_REGISTERED ) // 1
				     							 .or(d.status).is( DocumentObserverIntf._FILE_CHANGE_MOD ) // 2
				     							 .or(d.status).is( DocumentObserverIntf._FILE_CHANGE_NEW).toSQL();
				    sql = sql+" "; out.print(2, "\n"+sql+"\n");
				    */ 
				}
				if (mode == 1){
					docRecords = iciDb.from(d).where(d.status) 
											  .is( DocumentObserverIntf._FILE_CHANGE_MOVE ) // 11
											  .select();
				}
				if (mode == 2){
					docRecords = iciDb.from(d).where(d.status) 
											  .is( DocumentObserverIntf._FILE_CHANGE_RENAME ) // 12
											  .select();
				}
				if (mode == -1){
					docRecords = iciDb.from(d).where(d.status)
											  .is( DocumentObserverIntf._FILE_CHANGE_DEL )
											  .select();
					// also asking orphanes
					
				}
				docs.clear();
				if (docRecords!=null){
					
					if (mode>=0){
						for (int i = 0; i < docRecords.size(); i++) {
							dr = docRecords.get(i);
							filename = dr.name;

							String hostFolder = iciDb.from(f).where(f.id).is(dr.folderid).select().get(0).location;

							filepath = DFutils.createPath(hostFolder, filename);
							
							if (DFutils.fileExists(filepath)) {
								docs.add( dr.docid, filepath);
								
								iciDb.from(d).set(d.status).to(DocumentObserverIntf._STATUS_FILE_HANDLING)
								             .where(d.docid).is(dr.docid)
								             .update();  
							}
							if (docs.size()>=maxCount){
								break;
							}
						}// i->
						connection.commit() ; // make it for all users !!
					}
				} // any result
				
				connection.close();
				
			}catch(Exception e){
				e.printStackTrace();
			}

		}catch(Exception e){
			e.printStackTrace();
		}
		
		
		return docs;
	}

	 
 


	
}











